package com.ar4j.bench.simplejdbc.service;

import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Service;

import com.ar4j.bench.common.domain.IItem;
import com.ar4j.bench.common.service.IItemService;
import com.ar4j.bench.jdbc.domain.Item;

/**
 * An item service implemented using spring's SimpleJdbcTemplate
 */
@Service
public class ItemSimpleJdbcService extends SimpleJdbcDaoSupport implements IItemService {
  private static final String ITEM_BY_ID = "SELECT ID, PRODUCT_ID, LIST_PRICE, UNIT_COST, NAME, IMAGE, DESCRIPTION FROM ITEM WHERE ID = ?";
  private static final String ITEMS_BY_PRODUCT_ID = "SELECT ID, PRODUCT_ID, LIST_PRICE, UNIT_COST, NAME, IMAGE, DESCRIPTION FROM ITEM WHERE PRODUCT_ID = ?";
  private static final String DELETE_BY_ID = "DELETE FROM ITEM WHERE ID = ?";
  private static final String UPDATE_SQL = "UPDATE ITEM SET PRODUCT_ID = :productId, LIST_PRICE = :listPrice, UNIT_COST = :unitCost, NAME = :name, IMAGE = :image, DESCRIPTION = :description WHERE ID = :id";
  private static final RowMapper<Item> MAPPER = ParameterizedBeanPropertyRowMapper.newInstance(Item.class); 
  
  private SimpleJdbcInsert insert;

  @Override
  public Long addItem(IItem item) {
    SqlParameterSource params = new BeanPropertySqlParameterSource(item);
    Number key = insert.executeAndReturnKey(params);
    return key.longValue();
  }

  @Override
  public IItem findById(Long id) {
    return getSimpleJdbcTemplate().queryForObject(ITEM_BY_ID, MAPPER, id);
  }

  @Override
  public List<IItem> findByProductId(Long productId) {
    List<Item> items = getSimpleJdbcTemplate().query(ITEMS_BY_PRODUCT_ID, MAPPER, productId);
    return new ArrayList<IItem>(items);
  }

  @Override
  public void removeById(Long id) {
    getSimpleJdbcTemplate().update(DELETE_BY_ID, id);
  }

  @Override
  public void updateItem(IItem item) {
    SqlParameterSource params = new BeanPropertySqlParameterSource(item);
    getSimpleJdbcTemplate().update(UPDATE_SQL, params);
  }

  @Override
  protected void initDao() throws Exception {
    super.initDao();
    
    insert = new SimpleJdbcInsert(getDataSource())
      .withTableName("ITEM")
      .usingColumns("PRODUCT_ID", "LIST_PRICE", "UNIT_COST", "NAME", "IMAGE", "DESCRIPTION")
      .usingGeneratedKeyColumns("ID");

  }

  @Autowired
  public void setAutowiredDataSource(DataSource dataSource) {
    setDataSource(dataSource);
  }
  
}
